﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace RoleHelper
{
    class valkyrdbaccess
    {
        private string source;
        private string tabla;
        private string query;
        private string server;
        private string database;
        private string user;
        private string password;
        private string port;
        private bool conectado;
        private MySql.Data.MySqlClient.MySqlConnection con;
        private List<string> campos;
        private List<string> datos;
        //IP oficial:84.120.224.213;
        //Connection string:source = "Server=host; Database=schema; Uid=user; Pwd=contraseña; Port=3306;";

        public valkyrdbaccess()
        {
            conectado = false;
        }

        //Para conectar a la base de datos oficial (PC Tauri);
        public void crearPred(string dtbase, string usr, string pass)
        {
            server = "84.120.224.213";
            port = "3306";
            database = dtbase;
            user = usr;
            password = pass;
        }
        //Opcion no gracias soy pro;
        public void crarAv(string servr, string dtbase, string usr, string pass, string p)
        {
            server = servr;
            database = dtbase;
            user = usr;
            password = pass;
            port = p;
        }

        ~valkyrdbaccess()
        {
            if (campos != null)
            {
                campos.Clear();
            }
            if (datos != null)
            {
                datos.Clear();
            }
            cerrar();
        }
        //De momento solo inserta strings.
        //TODO: diferenciar cuando es un int para que no reviente la bd.
        public void crearInsert(List<string> fields, List<string> data, string table)
        {
            campos = fields;
            datos = data;
            tabla = table;
            query = "INSERT INTO " + tabla + " (";
            for (int i = 0; i < fields.Count; i++)
            {
                if (i == fields.Count - 1)
                {
                    query += fields[i] + ")";
                }
                else
                {
                    query += fields[i] + ", ";
                }
            }
            query += " VALUES (";
            for (int i = 0; i < data.Count; i++)
            {
                if (i == data.Count - 1)
                {
                    query += "'" + data[i] + "');";
                }
                else
                {
                    query += "'" + data[i] + "', ";
                }
            }
        }

        public bool conectar()
        {
            try
            {
                //Connection string:source = "Server=host; Database=schema; Uid=user; Pwd=contraseña; Port=3306;";
                source = "Server=" + server + "; Database=" + database + "; Uid=" + user + "; Pwd=" + password + "; Port="+port+";";
                con = new MySql.Data.MySqlClient.MySqlConnection(source);
                con.Open();
                conectado = true;
                return true;
            }
            catch
            {
                return false;
            }
        }

        public bool ejecutarInsert()
        {
            try
            {
                MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(query, con);
                cmd.ExecuteNonQuery();
                return true;
            }
            catch
            {
                return false;
            }
        }

        public bool cerrar()
        {
            try
            {
                con.Close();
                conectado = false;
                return true;
            }
            catch
            {
                return false;
            }
        }

        public void queryacapon(string taka)
        {
            query = taka;
        }

        public string getquery()
        {
            return query;
        }

        public List<List<string>> ejecutarSelect()
        {
            List<List<string>> retorno = new List<List<string>>();
            MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(query, con);
            MySql.Data.MySqlClient.MySqlDataReader read = cmd.ExecuteReader();
            while (read.Read())
            {
                List<string> fila = new List<string>();
                for (int i = 0; i < campos.Count; i++)
                {
                    fila.Add(read[i].ToString());
                }
                retorno.Add(fila);
            }
            read.Close();
            return retorno;
        }

        public string getdatabase()
        {
            return database;
        }

        public bool getconectado()
        {
            return conectado;
        }

        private string getParteSelect(List<string> c, string t)
        {
            string q;
            campos = c;
            q = "SELECT ";
            for (int i = 0; i < campos.Count; i++)
            {
                if (i == campos.Count - 1)
                {
                    q += campos[i];
                }
                else
                {
                    q += campos[i] + ", ";
                }
            }
            q += " FROM " + t + " ";
            return q;
        }

        public void crearSimpleSelect(List<string> campos, string table)
        {
            query = getParteSelect(campos, table)+";";
        }

        public void crearComlexSelect(List<string> c, string table, List<string> w)
        {
            query = getParteSelect(c, table);
            query += " WHERE ";
            for (int i = 0; i < w.Count; i++)
            {
                if (i == w.Count - 1)
                {
                    query += w[i];
                }
                else
                {
                    query += w[i] + ", ";
                }
            }
            query += ";";
        }

    }
}
